Welcome to Preparatory Labs for Databases

1 Course Overview

1.1 Course Description

This SQL Bootcamp is designed to teach students, particularly those in applied business analytics and data science, the foundations and advanced techniques of SQL using both MySQL and PostgreSQL. Over nine modules, you will master fundamental SQL concepts such as querying data, working with joins, stored procedures, transactions, and more. Each module is accompanied by self-guided learning tasks, quizzes, and practical assessments to ensure a deep understanding of database concepts.

1.2 Learning Outcomes

Upon completing the SQL Bootcamp, you will be able to:

  • Understand and write basic SQL queries.
  • Retrieve, manipulate, and manage data efficiently.
  • Work with multiple tables using joins and set operations.
  • Create and manage databases, tables, and indexes.
  • Understand and implement stored procedures, functions, triggers, and transactions.

1.3 Total Time Required

  • Instructor-led learning: 2 hours (each module for 20 minutes)
  • Self-guided learning and assessments: 4 hours combined

2 SQL Course Outline

2.1 Getting Started with SQL

  • Objectives:
    • Introduction to relational databases, setting up MySQL and PostgreSQL, understanding SQL syntax and data types.
  • Lectures:
    1. Introduction to MySQL and PostgreSQL: Overview of popular relational databases and setting up local environments.
    2. Data Types: Understanding core SQL data types (e.g., int, varchar, date, etc.).
    3. Creating Databases and Tables: Steps to create a new database and define tables.
    4. Inserting, Updating, and Deleting Records: Basic SQL operations for manipulating records.

2.2 Retrieving Data with SQL

  • Objectives:
    • Learn to write SQL queries to retrieve data from a database.
  • Lectures:
    1. SELECT Statements: Introduction to the SELECT statement for querying data.
    2. WHERE Clauses and Filtering Data: Using WHERE to filter query results based on conditions.
    3. Sorting Data with ORDER BY, LIMIT, and OFFSET: Techniques for ordering and limiting results.
    4. Using Variables in Queries: Working with variables to enhance query flexibility.

2.3 Modifying Data with SQL

  • Objectives:
    • Master techniques for modifying data in SQL databases.
  • Lectures:
    1. INSERT, UPDATE, DELETE Statements: Essential commands for data manipulation.
    2. Error Handling and Constraints: Handling constraints like PRIMARY KEY and FOREIGN KEY, and error management.
    3. Managing NULL Values and Default Values: Handling missing or default values in SQL.
    4. String Operations in SQL: Useful string functions like CONCAT, SUBSTRING, etc.

2.4 Joins and Advanced Queries

  • Objectives:
    • Learn to retrieve and work with data across multiple tables using SQL joins.
  • Lectures:
    1. INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN: Understanding different types of joins and their uses.
    2. UNION and UNION ALL: Combining results from multiple queries.
    3. Grouping and Aggregating Data with GROUP BY: Techniques for grouping data and using aggregate functions like SUM, COUNT, etc.
    4. HAVING Clauses: Filtering aggregated data with HAVING.

2.5 Managing Database Structures

  • Objectives:
    • Learn to modify and optimize database structures for improved performance.
  • Lectures:
    1. ALTER TABLE and MODIFY Operations: Altering table structures and modifying columns.
    2. Creating and Deleting Indexes: Indexing strategies for faster data retrieval.
    3. Optimizing Queries for Performance: Techniques to improve query execution times.
    4. Handling Large Datasets and Backups: Managing large datasets, backups, and recovery

2.6 Stored Procedures and Triggers

  • Objectives:
    • Introduction to automation and server-side programming with stored procedures and triggers.e
  • Lectures:
    1. Creating and Calling Stored Procedures: Writing reusable procedures for SQL operations.
    2. Creating and Managing Triggers: Automating actions with database triggers.
    3. Error Handling in Stored Procedures: Managing errors within stored procedures.
    4. Performance Considerations: Best practices for efficient stored procedures and triggers.